package com.glsc.ngateway.common.base.repo.mysql.gateway.platform;


import com.glsc.ngateway.common.base.domain.mysql.gateway.platform.Visit;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

@Repository
public interface VisitRepo extends JpaRepository<Visit, Long>, JpaSpecificationExecutor<Visit> {


    @Query(value = "select v.systemId as systemId, count(v.systemId) as count from Visit v where v.accessDate = :date group by v.systemId")
    List<Map> countByDate(String date);


    @Query(value = "select v.userName as userName, count(v.userName) as count from Visit v where v.systemId = :sysName and v.accessDate = :date group by v.userName")
    List<Map> countByUser(String sysName, String date);


    @Query(value = "select v.systemId as systemId, count(v.systemId) as count from Visit v where v.accessDate like CONCAT(:month,'%') group by v.systemId")
    List<Map> countByMonth(String month);


    @Query(value = "select v.userName as userName, count(v.userName) as count from Visit v where v.systemId = :sysName and v.accessDate like CONCAT(:month,'%') group by v.userName")
    List<Map> countMonthBySys(String sysName, String month);


    @Query(value = "select new Visit(v.id, v.systemId, v.accessDate,v.userName, v.token, v.accessTime, v.expireTime) from Visit v " +
            "where v.accessTime in (select max(v.accessTime) from Visit v " +
            "where v.userName in (select distinct v.userName from Visit v) group by v.userName)")
    Page<Visit> findUserData(Pageable pageable);

    @Query(value = "select distinct v.systemId from Visit v")
    List<String> findAllSys();
}
